* Prepare data 1

********************************************************************************
*  Get Data for Violent Crime Autoregressions
clear all
use sf_long_c

mvdecode _all, mv(-9999)

rename  pm10x24nidw pm10
rename  vcrimerate v_crime

xtset property_id year

gen Lpm10 = L.pm10
gen Lv_crime = L.v_crime

drop if county == 75 | county ==.
gen market = county
replace market = 2 if county == 13
replace market = 3 if county == 41
replace market = 4 if county == 81
replace market = 5 if county == 85

gen t = year-1989

keep  v_crime Lv_crime t market Lpm10
drop if Lv_crime == .
outfile  market v_crime Lv_crime t Lpm10 using tprob_data, comma wide nolabel replace 

********************************************************************************
*  Get Data for Homicide Autoregressions
clear all
import delimited murder.txt

rename v1 murder1990

local i = 1991
foreach x of varlist v2-v19{
rename `x' murder`i'
local i = `i' + 1
}

gen county = _n
reshape long murder, i(county) j(year)

outfile murder year using murder_data, comma wide nolabel replace

********************************************************************************
*  Get Data for TSP Autoregressions
clear all
use TSPquicklook.dta, clear

collapse (mean) geometricmean [aweight= observationcount], by(year state county)

gen double scounty = state*1000 + county

xtset scounty year

gen L_geometricmean = L.geometricmean
gen LL_geometricmean = L2.geometricmean
keep if L_geometricmean ~= .
outfile geometricmean L_geometricmean year using tsp_data, comma wide nolabel replace
keep if LL_geometricmean ~= .
outfile geometricmean L_geometricmean LL_geometricmean year using tsp_data_rob, comma wide nolabel replace
